/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.salary.bean;

import com.ibatis.sqlmap.client.SqlMapClient;
import com.yunhoo.salary.db.impl.WageImpl;
import com.yunhoo.salary.model.SalaField;
import com.yunhoo.salary.model.WageItem;
import com.yunhoo.salary.util.SalaToolKit;
import com.yunhoo.salary.util.SalaXmlParser;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.CommonParam;
import com.yunhoo.sys.util.common.LoginParam;
import com.yunhoo.sys.util.common.Null;
import com.yunhoo.sys.util.common.ToolKit;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.component.html.HtmlColumn;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.component.html.HtmlOutputText;
import javax.faces.context.FacesContext;
import javax.faces.event.ValueChangeEvent;
import javax.faces.model.SelectItem;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CreationHelper;

/**
 * 个人工资
 *
 * @author Administrator
 */
@ManagedBean(name = "psnSalaBean")
@SessionScoped
public class PsnSalaBean {

    private String acct = "";//账套号
    private String salaType = "";//工资类型，离退机构或在职
    private Integer year = 0;//年度
    private Integer wageYear = 0;//薪酬年度
    private String salaYear = "";//
    private Integer month = 1; //月份
    private Object[][] data;
    private Object[] wage;//个人工资条 
    private List<LinkedList> ledger = new LinkedList();//个人工资台账
    private List<List> sala = new ArrayList();//个人月度工资
    private List<WageItem> psnWage = new LinkedList<WageItem>();//个人工资条
    private List<String> fields = new LinkedList<String>();// 工资项目字段
    private List<String> fieldLabels = new LinkedList<String>();//工资项目名称
    private Map salaItems = null;
    private HtmlDataTable monthSala = new HtmlDataTable();
    private SalaXmlParser parser = new SalaXmlParser();
    private SelectItem[] monthes = null;
    private List<SelectItem> salaMonthes = new ArrayList<SelectItem>();
    private List<SelectItem> iyears = new ArrayList<SelectItem>();
    private List<SelectItem> psnWageYears = new ArrayList<SelectItem>();//个人薪酬年度
    private List<SelectItem> accts = new ArrayList<SelectItem>();
    private String empProp = "";
    private int dateLenght = 0;
    private int[] index = null;
    //查询范围
    private Date beginDate = new Date();
    private Date endDate = new Date();
    //
    private List<String> fields_query = new LinkedList<String>();// 工资项目字段
    private List<String> fieldLabels_query = new LinkedList<String>();//工资项目名称
    private List<LinkedList> ledger_query = new LinkedList();//个人查询 -- 跨年
    //
    private List<String> fields_dept = new LinkedList<String>();// 工资项目字段
    private List<String> fieldLabels_dept = new LinkedList<String>();//工资项目名称
    private List<LinkedList> ledger_dept = new LinkedList();//个人查询 -- 跨年
    //
    private List<String> fields_all = new LinkedList<String>();// 工资项目字段
    private List<String> fieldLabels_all = new LinkedList<String>();//工资项目名称
    private List<LinkedList> ledger_all = new LinkedList();//个人查询 -- 跨年

    public PsnSalaBean() {
        initSalaItemInfo();
        initSalaItemInfo_query();
        initSalaItemInfo_dept();
        initSalaItemInfo_all();
        initMonthList();
        initAccts();//帐套信息
        wageYear = CommonParam.defaultYear();
        psnYearWage();
        wageQuery();
    }

    public void acctChanged() {
        acct = WageImpl.salaAcct(salaType);
        Map map = new HashMap();
        map.put("salaType", salaType);
        map.put("accid", acct);
        if (iyears != null && iyears.size() > 0) {
            iyears.clear();
        }
        List<Integer> li = WageImpl.salaYears(map);
        if (li != null && li.size() > 0) {
            for (int _year : li) {
                iyears.add(new SelectItem(_year, _year + "年"));
            }
        }
    }

    public void acctChanged_(ValueChangeEvent evt) {
        salaType = ((String) evt.getNewValue()).trim();
        acct = WageImpl.salaAcct(salaType);
        Map map = new HashMap();
        map.put("salaType", salaType);
        map.put("accid", acct);
        if (iyears != null && iyears.size() > 0) {
            iyears.clear();
        }
        List<Integer> li = WageImpl.salaYears(map);
        if (li != null && li.size() > 0) {
            for (int _year : li) {
                iyears.add(new SelectItem(_year, _year + "年"));
            }
        }
    }

    public void yearChanged() {
        System.out.println("年度:" + salaYear);
        List<Integer> monthli = WageImpl.monthList(salaYear);
        if (salaMonthes != null && salaMonthes.size() > 0) {
            salaMonthes.clear();
        }
        for (Integer ml : monthli) {
            salaMonthes.add(new SelectItem(ml, ml + "月"));
        }
    }

    private void initAccts() {
        accts.add(new SelectItem("main", "在职"));
        accts.add(new SelectItem("lt", "离退休"));
    }

    private void initMonthList() {
        monthes = new SelectItem[12];
        for (int i = 0; i < 12; i++) {
            //int month = i + 1;
            monthes[i] = new SelectItem(i + 1, i + 1 + "月");
        }
    }

    private void initSalaItemInfo_all() {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("empProp", this.getEmpProp());
        List<HashMap<String, String>> items = WageImpl.wageItems(map);
        fieldLabels_all.add("部门");
        fieldLabels_all.add("姓名");
        fieldLabels_all.add("年度");
        fieldLabels_all.add("月份");
        fields_all.add("cDept_name");//查询语句首字段：月份
        fields_all.add("cPsn_name");//查询语句首字段：月份
        fields_all.add("iyear");//查询语句首字段：月份
        fields_all.add("iMonth");//查询语句首字段：月份 
        for (HashMap<String, String> item : items) {
            fields_all.add("F_" + String.valueOf(item.get("iGZItem_id")));
            fieldLabels_all.add(item.get("cSetGZItemName").trim());
        }
    }

    private void initSalaItemInfo_dept() {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("empProp", this.getEmpProp());
        List<HashMap<String, String>> items = WageImpl.wageItems(map);
        fieldLabels_dept.add("姓名");
        fieldLabels_dept.add("年度");
        fieldLabels_dept.add("月份");
        fields_dept.add("cPsn_name");//查询语句首字段：月份
        fields_dept.add("iyear");//查询语句首字段：月份
        fields_dept.add("iMonth");//查询语句首字段：月份 
        for (HashMap<String, String> item : items) {
            fields_dept.add("F_" + String.valueOf(item.get("iGZItem_id")));
            fieldLabels_dept.add(item.get("cSetGZItemName").trim());
        }
    }

    /**
     * 初始化工资项目信息，包括字段名、字段
     */
    private void initSalaItemInfo_query() {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("empProp", this.getEmpProp());
        List<HashMap<String, String>> items = WageImpl.wageItems(map);
        fieldLabels_query.add("年度");
        fieldLabels_query.add("月份");
        fields_query.add("iyear");//查询语句首字段：月份
        fields_query.add("iMonth");//查询语句首字段：月份 
        for (HashMap<String, String> item : items) {
            fields_query.add("F_" + String.valueOf(item.get("iGZItem_id")));
            fieldLabels_query.add(item.get("cSetGZItemName").trim());
        }
    }

    /**
     * 初始化工资项目信息，包括字段名、字段
     */
    private void initSalaItemInfo() {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("empProp", this.getEmpProp());
        List<HashMap<String, String>> items = WageImpl.wageItems(map);
        salaItems = new LinkedHashMap<String, String>();
        fieldLabels.add("月份");
        fields.add("iMonth");//查询语句首字段：月份
        salaItems.put("iMonth", "月份");
        for (HashMap<String, String> item : items) {
            salaItems.put("F_" + String.valueOf(item.get("iGZItem_id")), item.get("cSetGZItemName"));
            fields.add("F_" + String.valueOf(item.get("iGZItem_id")));
            fieldLabels.add(item.get("cSetGZItemName").trim());
        }
    }

    public void genPsnWageResult() {
        Map params = new HashMap();
        params.put("orgCode", LoginParam.getOrg().getCode());
        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
        params.put("iyear", salaYear);//
        params.put("sqlStr", sqlStr());
        params.put("imonth", month);
        params.put("empProp", this.getEmpProp());
        List<Map> result = WageImpl.monthPsnWage(params);
        dateLenght = fields.size();
        data = new Object[2][dateLenght];
        wage = new Object[dateLenght];
        for (Map dd : result) {
            for (int i = 0; i < fields.size(); i++) {
                data[0][i] = fieldLabels.get(i);
                data[1][i] = dd.get(fields.get(i));
            }
        }
        System.out.println("data : " + data);
    }

    public void impSalaData() {
        boolean ok = true;
        SqlMapClient client = IbatisTemplate.getSqlMapClient();
        try {
            delData(client);
            importData(client);
        } catch (SQLException ex) {
            ok = false;
            Logger.getLogger(PsnSalaBean.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (ok) {
            ToolKit.msgs_info(FacesContext.getCurrentInstance(), null, "导入完成!");
        } else {
            ToolKit.msgs_err(FacesContext.getCurrentInstance(), null, "导入失败!");
        }
    }

    private void importData(SqlMapClient client) throws SQLException {
        Map map = new HashMap();
        SalaToolKit kit = new SalaToolKit("sala");
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("dbServer", kit.getDbServer().trim());
        map.put("uid", kit.getUid().trim());
        map.put("pwd", kit.getPwd().trim());
        map.put("dbName", "UFDATA_" + acct.trim() + "_" + String.valueOf(year).trim());
        map.put("imonth", this.getMonth());
        map.put("empProp", dbProp());
        //WageImpl.importFromRemoteServer(map);
        client.insert("wage_u8." + "importFromRemoteServer_old", map);
    }

    private void delData(SqlMapClient client) throws SQLException {
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("imonth", this.getMonth());
        map.put("iyear", year);
        map.put("empProp", dbProp());
        //WageImpl.deleteData(map);
        client.delete("wage." + "deleteData", map);
    }

    public String dbProp() {
        String dbProp = "";
        if ("lt".equals(salaType.trim())) {
            dbProp = "_wage";
        }
        return dbProp;
    }

    public void wageQuery_all() {
        if (ledger_all != null) {
            ledger_all.clear();
        }
        String dateParam = ToolKit.dataRangeQueryParam(beginDate, endDate, null);
        if (dateParam == null) {
            return;
        }
        System.out.println("sqlStr_sum : " + sqlStr_sum());
        Map params = new HashMap();
        params.put("orgCode", LoginParam.getOrg().getCode());
        params.put("iyear", LoginParam.getIyear());
        params.put("empProp", this.getEmpProp());
        params.put("sqlStr", sqlStr_sum());
        params.put("dateParam", dateParam);

        List<Map> result = IbatisTemplate.queryForList("wage.wageQuery_all", params);
        if (result != null && result.size() > 0) {
            for (Map line : result) {
                LinkedList monthWage = new LinkedList();
                for (int i = 0; i < fields_all.size(); i++) {
                    Object content = line.get(fields_all.get(i));
                    if (content != null) {
                        monthWage.add(content);
                    } else {
                        monthWage.add("");
                    }
                }
//                if (monthWage.size() > 0) {
//                    if ("".equals(monthWage.get(3))) {//剔除月工资，只留合计
                ledger_all.add(monthWage);
//                    }
//                }
            }
        }
        ledger_all.get(0).set(0, "总计");
        System.out.println("ledger : " + ledger_all.size());
    }

    public void wageQuery_dept() {
        if (ledger_dept != null) {
            ledger_dept.clear();
        }
        String dateParam = ToolKit.dataRangeQueryParam(beginDate, endDate, null);
        if (dateParam == null) {
            return;
        }
        System.out.println("sqlStr_sum : " + sqlStr_sum());
        Map params = new HashMap();
        params.put("orgCode", LoginParam.getOrg().getCode());
        params.put("deptCode", LoginParam.getDept().getCode());
        params.put("empProp", this.getEmpProp());
        params.put("sqlStr", sqlStr_sum());
        params.put("dateParam", dateParam);

        List<Map> result = IbatisTemplate.queryForList("wage.wageQuery_dept", params);
        if (result != null && result.size() > 0) {
            dateLenght = fields_dept.size();
            for (Map line : result) {
                LinkedList monthWage = new LinkedList();
                for (int i = 0; i < fields_dept.size(); i++) {
                    Object content = line.get(fields_dept.get(i));
                    if (content != null) {
                        monthWage.add(content);
                    } else {
                        monthWage.add("");
                    }
                }
//                if (monthWage.size() > 0) {
//                    if ("".equals(monthWage.get(2))) {//剔除月工资，只留合计                
                ledger_dept.add(monthWage);
//                    }
//                }
            }
        }
        ledger_dept.get(0).set(0, "总计");
        System.out.println("ledger : " + ledger_dept.size());
    }

    /*
     *个人工资查询--跨年
     */
    public void wageQuery() {
        if (ledger_query != null) {
            ledger_query.clear();
        }
        String dateParam = ToolKit.dataRangeQueryParam(beginDate, endDate, null);
        if (dateParam == null) {
            return;
        }
        Map params = new HashMap();
        params.put("orgCode", LoginParam.getOrg().getCode());
        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
        params.put("empProp", this.getEmpProp());
        params.put("sqlStr", sqlStr_sum());
        params.put("dateParam", dateParam);

        List<Map> result = IbatisTemplate.queryForList("wage.wageQuery", params);
        if (result != null && result.size() > 0) {
            dateLenght = fields_query.size();
            for (Map line : result) {
                LinkedList monthWage = new LinkedList();
                for (int i = 0; i < fields_query.size(); i++) {
                    Object content = line.get(fields_query.get(i));
                    if (content != null) {
                        monthWage.add(content);
                    } else {
                        monthWage.add("");
                    }
                }
//                if (monthWage.size() > 0) {
//                    if (!"".equals(monthWage.get(1))) {
//                        monthWage.set(0, "");
//                    }
                ledger_query.add(monthWage);
                //}
            }
        }
        System.out.println("ledger_query null : " + (ledger_query == null));
        System.out.println("ledger_query empty :" + (ledger_query.isEmpty()));
        System.out.println("ledger_query isze : " + ledger_query.size());
        if (ledger_query != null && ledger_query.size() > 0) {
            System.out.println("设置总计 ！！");
            ledger_query.get(0).set(0, "总计");
        }
    }

    public String sqlStr_sum() {
        //String sqlStr = iyear,iMonth,sum(F_8) F_8,sum(F_28) F_28,sum(F_9) F_9,sum(F_10) F_10,sum(F_11) F_11,sum(F_12) F_12,sum(F_13) F_13,sum(F_14) F_14,sum(F_20) F_20,sum(F_21) F_21,sum(F_15) F_15,sum(F_22) F_22,sum(F_16) F_16,sum(F_27) F_27,sum(F_1) F_1,sum(F_17) F_17,sum(F_18) F_18,sum(F_19) F_19,sum(F_6) F_6,sum(F_2) F_2,sum(F_3) F_3,sum(F_25) F_25,sum(F_23) F_23,' ' F_26
        String sqlStr = "iyear,iMonth,";
        for (int i = 2; i < fields_query.size(); i++) {
            if (!"F_26".equals(fields_query.get(i))) {
                sqlStr += "sum(" + fields_query.get(i) + ") " + fields_query.get(i) + ",";
            }
        }
        sqlStr = sqlStr.substring(0, sqlStr.length() - 1);
        return sqlStr;
    }

    /**
     * 个人年度工资台账
     */
    public void initPsnWageYears() {
        Map map = new HashMap();
        map.put("empProp", this.getEmpProp());
        map.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
        map.put("orgCode", LoginParam.getOrg().getCode());
        List<Integer> years = WageImpl.iyearList(map);
        for (Integer year : years) {
            psnWageYears.add(new SelectItem(year.toString(), year.toString()));
        }
    }

    public void psnYearWage() {
        if (ledger != null) {
            ledger.clear();
        }
        Map params = new HashMap();
        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
        params.put("iyear", wageYear);
        params.put("sqlStr", sqlStr());
        params.put("empProp", this.getEmpProp());
        params.put("orgCode", LoginParam.getOrg().getCode());
        List<Map> result = WageImpl.psnWageLedger(params);
        if (result != null && result.size() > 0) {
            dateLenght = fields.size();
            for (Map line : result) {
                LinkedList monthWage = new LinkedList();
                for (int i = 0; i < fields.size(); i++) {
                    Object content = line.get(fields.get(i));
                    if (content != null) {
                        monthWage.add(content);
                    } else {
                        monthWage.add("");
                    }
                }
                if (monthWage.size() > 0) {
                    ledger.add(monthWage);
                }
            }
        }
        ledger.add(yearWageSum());
        System.out.println("ledger : " + ledger);
    }

    public LinkedList yearWageSum() {
        Map params = new HashMap();
        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
        params.put("iyear", wageYear);
        params.put("fields", salaSumFields());
        params.put("empProp", this.getEmpProp());
        params.put("orgCode", LoginParam.getOrg().getCode());
        List<Map> result = WageImpl.wageSumByYear(params);
        LinkedList total = new LinkedList();
        if (result != null) {
            Map sum = result.get(0);
            for (int i = 0; i < fields.size(); i++) {
                total.add(sum.get(fields.get(i)));
            }
        }
        return total;
    }

    public String salaSumFields() {
        Map map = new HashMap();
        map.put("empProp", this.getEmpProp());
        map.put("orgCode", LoginParam.getOrg().getCode());
        List<SalaField> fields = WageImpl.salaFields(map);
        String wageFields = "'合计' iMonth,";

        for (SalaField field : fields) {
            if (field.getFieldStyle() == 0) {
                wageFields += "sum(" + field.getFiledEng().trim() + ") " + field.getFiledEng().trim() + ",";
            }
            if (field.getFieldStyle() == 1) {
                wageFields += "' ' " + field.getFiledEng().trim() + ",";
            }
        }
        return wageFields.substring(0, wageFields.length() - 1);
    }

    public String sqlStr() {
        //String sqlStr = "rtrim(cast(imonth as char(2))) + '月' imonth,";
        String sqlStr = "";
        for (String field : fields) {
            sqlStr += field + ",";
        }
        sqlStr = sqlStr.substring(0, sqlStr.length() - 1);
        return sqlStr;
    }

    public List<SelectItem> getSalaMonthes() {
        return salaMonthes;
    }

    public void setSalaMonthes(List<SelectItem> salaMonthes) {
        this.salaMonthes = salaMonthes;
    }

    public Integer getMonth() {
        return month;
    }

    public void setMonth(Integer month) {
        this.month = month;
    }

    public List<String> getFields() {
        return fields;
    }

    public void setFields(List<String> fields) {
        this.fields = fields;
    }

    public List<String> getFieldLabels() {
        return fieldLabels;
    }

    public void setFieldLabels(List<String> fieldLabels) {
        this.fieldLabels = fieldLabels;
    }

    public Map getSalaItems() {
        return salaItems;
    }

    public void setSalaItems(Map salaItems) {
        this.salaItems = salaItems;
    }

    public List<LinkedList> getLedger() {
        return ledger;
    }

    public void setLedger(List<LinkedList> ledger) {
        this.ledger = ledger;
    }

    public HtmlDataTable getMonthSala() {
        return monthSala;
    }

    public void setMonthSala(HtmlDataTable monthSala) {
        this.monthSala = monthSala;
    }

    public List<List> getSala() {
        return sala;
    }

    public void setSala(List<List> sala) {
        this.sala = sala;
    }

    public Object[][] getData() {
        return data;
    }

    public void setData(Object[][] data) {
        this.data = data;
    }

    public SelectItem[] getMonthes() {
        return monthes;
    }

    public void setMonthes(SelectItem[] monthes) {
        this.monthes = monthes;
    }

    public List<WageItem> getPsnWage() {
        return psnWage;
    }

    public void setPsnWage(List<WageItem> psnWage) {
        this.psnWage = psnWage;
    }

    public Object[] getWage() {
        return wage;
    }

    public void setWage(Object[] wage) {
        this.wage = wage;
    }

    public String getEmpProp() {
        String prop = LoginParam.getPer().getEmpProp().trim();
        if (prop.equalsIgnoreCase("1")) {
            empProp = "_wage";
        }
        return empProp;
    }

    public void setEmpProp(String empProp) {
        this.empProp = empProp;
    }

    public String getAcct() {
        return acct;
    }

    public void setAcct(String acct) {
        this.acct = acct;
    }

    public List<SelectItem> getAccts() {
        return accts;
    }

    public void setAccts(List<SelectItem> accts) {
        this.accts = accts;
    }

    public List<SelectItem> getIyears() {
        return iyears;
    }

    public void setIyears(List<SelectItem> iyears) {
        this.iyears = iyears;
    }

    public Integer getYear() {
        return year;
    }

    public void setYear(Integer year) {
        this.year = year;
    }

    public String getSalaType() {
        return salaType;
    }

    public void setSalaType(String salaType) {
        this.salaType = salaType;
    }

    public List<SelectItem> getPsnWageYears() {
        if (psnWageYears == null || (psnWageYears != null && psnWageYears.size() == 0)) {
            initPsnWageYears();
        }
        return psnWageYears;
    }

    public void setPsnWageYears(List<SelectItem> psnWageYears) {
        this.psnWageYears = psnWageYears;
    }

    public Integer getWageYear() {
        return wageYear;
    }

    public void setWageYear(Integer wageYear) {
        this.wageYear = wageYear;
    }

    public String getSalaYear() {
        return salaYear;
    }

    public void setSalaYear(String salaYear) {
        this.salaYear = salaYear;
    }

    /*
     * 以下废弃
     */
    //查询结果映射为xml文件，此方法作废。
//    public void queryItems() throws Exception {
//        if (salaXmlStr() != null) {
//            sala = parser.getPsnSalaByMonth(fields, salaItems, salaXmlStr());
//        }
//        if (sala != null) {
//            data = new Object[1][fields.size()];
//            wage = new Object[fields.size()];
//            for (int i = 0; i < fields.size(); i++) {
//                //data[i][0] = fieldLabels.get(i);
//                data[0][i] = sala.get(i);
//                wage[i] = sala.get(i);
//            }
//        }
//        genPsnWageResult();
//    }
//    public void ledgerForYear() {
//        List<String> xmlFiles = salaXmlList();
//        xmlFiles.add(salaSum());
//        ledger = parser.getPsnSalaLedger(fields, xmlFiles);
//    }
//    public List<String> salaXmlList() {
//        Map params = new HashMap();
//        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
//        params.put("iyear", "2011");
//        params.put("sqlStr", sqlStr());
//        params.put("empProp", this.getEmpProp());
//        List<String> result = WageImpl.psnSalaLedger(params);
//        return result;
//    }
    /**
     * 查询的结果输出格式为XML
     */
//    public String salaXmlStr() {
//        Map params = new HashMap();
//        params.put("cpsn_number", LoginParam.getPer().getCpsn_number().trim());
//        params.put("iyear", "2011");
//        params.put("sqlStr", sqlStr());
//        params.put("imonth", month);
//        params.put("empProp", this.getEmpProp());
//        List<String> result = WageImpl.salaByPerson(params);
//        String xml = "";
//        for (String c : result) {
//            xml += c;
//        }
//        System.out.println("Sala:" + xml);
//        return xml;
//    }
    public int getDateLenght() {
        return dateLenght;
    }
    private HtmlDataTable fiSalaryDetailTable = new HtmlDataTable();
    private String empName = "测试表头";

    public HtmlDataTable getFiSalaryDetailTable() {
        System.err.println("vSalaryDetail:=========:" + fiSalaryDetailTable);
        if (fiSalaryDetailTable == null) {
            fiSalaryDetailTable = new HtmlDataTable();
        }
        HtmlColumn column = new HtmlColumn();
        HtmlOutputText header = new HtmlOutputText();
        // header.setValueExpression("value", getValueExpression("#{psnSalaBean.empName}"));
        column.getChildren().add(header);
        fiSalaryDetailTable.getChildren().add(column);
        return fiSalaryDetailTable;
    }
//    private ValueExpression getValueExpression(String ve) {
//        return Expressions.instance().createValueExpression(ve).toUnifiedValueExpression();
//    }

    public void exportExcel() {
        if (data != null) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("个人工资条");
            // List<String> excelColumns = initTabeleHeader();
            Object[][] rows = data;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("个人工资条");
            // HSSFRow headerRow = demoSheet.createRow(0);
//        for (int i = 0; i < fieldLabels.size(); i++) {
//            HSSFCell headerCell = headerRow.createCell((short) i);
//            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
//            headerCell.setCellValue(excelColumns.get(i));
//        }
            //填充数据
            int rowIndex = 1;
            for (Object[] row : rows) {
                createTableRow(demoWorkBook, demoSheet, row, rowIndex, fieldLabels);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }

    //初始化表头信息
//    public List<String> initTabeleHeader() {
//        List<String> excelColumns = fieldLabels;
//        return excelColumns;
//    }
    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, Object[] data, int rowIndex, List<String> fieldLabels) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) fieldLabels.size()));
        cell1 = row1.createCell(0);
        cell1.setCellValue("个人工资条(单位/元) ");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        for (int i = 0; i < fieldLabels.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(data[i].toString());
        }
    }

    public void exportExcelAll() {
        if (!Null.check(ledger)) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("个人工资台账");
            List<String> excelColumns = initTabeleHeaderAll();
            List<LinkedList> rows = ledger;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("个人工资台账");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < fieldLabels.size(); i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns.get(i));
            }
            //填充数据
            int rowIndex = 2;
            for (LinkedList row : rows) {
                createTableRowAll(demoWorkBook, demoSheet, row, rowIndex, fieldLabels);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }

    //初始化表头信息
    public List<String> initTabeleHeaderAll() {
        List<String> excelColumns = fieldLabels;
        return excelColumns;
    }

    public static void createTableRowAll(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, LinkedList ledger, int rowIndex, List<String> fieldLabels) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) fieldLabels.size()));
        cell1 = row1.createCell(0);
        cell1.setCellValue("工资台帐(单位/元) ");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        for (int i = 0; i < fieldLabels.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(ledger.get(i).toString());
        }
    }

    public void exportExcelQuery() {
        if (!Null.check(ledger_query)) {
            HSSFWorkbook demoWorkBook = new HSSFWorkbook();
            HSSFSheet demoSheet = demoWorkBook.createSheet("个人工资");
            List<String> excelColumns = initTabeleHeaderQuery();
            List<LinkedList> rows = ledger_query;

            HSSFHeader header = demoSheet.getHeader();
            header.setCenter("个人工资");
            HSSFRow headerRow = demoSheet.createRow(1);
            for (int i = 0; i < fieldLabels_query.size(); i++) {
                HSSFCell headerCell = headerRow.createCell((short) i);
                //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
                headerCell.setCellValue(excelColumns.get(i));
            }
            //填充数据
            int rowIndex = 2;
            for (LinkedList row : rows) {
                createTableRowQuery(demoWorkBook, demoSheet, row, rowIndex, fieldLabels_query);
                rowIndex++;
            }
            exportExcel(demoWorkBook, demoSheet);
        }
    }

    //初始化表头信息
    public List<String> initTabeleHeaderQuery() {
        List<String> excelColumns = fieldLabels_query;
        return excelColumns;
    }

    public static void createTableRowQuery(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, LinkedList ledger, int rowIndex, List<String> fieldLabels) {
        //创建第rowIndex行
        //HSSFWorkbook workbook = new HSSFWorkbook();
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFFont font = demoWorkBook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        HSSFCellStyle cellStyle = demoWorkBook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        cellStyle.setFont(font);//选择需要用到的字体格式

        HSSFCell cell1 = null;
        HSSFRow row1 = demoSheet.createRow(0);
        demoSheet.addMergedRegion(new Region((short) 0, (short) 0, (short) 0, (short) fieldLabels.size()));
        cell1 = row1.createCell(0);
        cell1.setCellValue("个人工资(单位/元) ");
        cell1.setCellStyle(cellStyle);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        for (int i = 0; i < fieldLabels.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(ledger.get(i).toString());
        }
    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public SalaXmlParser getParser() {
        return parser;
    }

    public void setParser(SalaXmlParser parser) {
        this.parser = parser;
    }

    public int[] getIndex() {
        return index;
    }

    public void setIndex(int[] index) {
        this.index = index;
    }

    public Date getBeginDate() {
        return beginDate;
    }

    public void setBeginDate(Date beginDate) {
        this.beginDate = beginDate;
    }

    public Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public List<String> getFields_query() {
        return fields_query;
    }

    public void setFields_query(List<String> fields_query) {
        this.fields_query = fields_query;
    }

    public List<String> getFieldLabels_query() {
        return fieldLabels_query;
    }

    public void setFieldLabels_query(List<String> fieldLabels_query) {
        this.fieldLabels_query = fieldLabels_query;
    }

    public List<LinkedList> getLedger_query() {
        return ledger_query;
    }

    public void setLedger_query(List<LinkedList> ledger_query) {
        this.ledger_query = ledger_query;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public List<String> getFields_dept() {
        return fields_dept;
    }

    public void setFields_dept(List<String> fields_dept) {
        this.fields_dept = fields_dept;
    }

    public List<String> getFieldLabels_dept() {
        return fieldLabels_dept;
    }

    public void setFieldLabels_dept(List<String> fieldLabels_dept) {
        this.fieldLabels_dept = fieldLabels_dept;
    }

    public List<LinkedList> getLedger_dept() {
        return ledger_dept;
    }

    public void setLedger_dept(List<LinkedList> ledger_dept) {
        this.ledger_dept = ledger_dept;
    }

    public List<String> getFields_all() {
        return fields_all;
    }

    public void setFields_all(List<String> fields_all) {
        this.fields_all = fields_all;
    }

    public List<String> getFieldLabels_all() {
        return fieldLabels_all;
    }

    public void setFieldLabels_all(List<String> fieldLabels_all) {
        this.fieldLabels_all = fieldLabels_all;
    }

    public List<LinkedList> getLedger_all() {
        return ledger_all;
    }

    public void setLedger_all(List<LinkedList> ledger_all) {
        this.ledger_all = ledger_all;
    }
}
